﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using shuatibao.Tool;

namespace shuatibao.Web.Subject
{
    public class SubjectAction
    {
        #region 一级科目
        #region 添加科目
        public static void insertSubject(string subject)
        {
            // 连接数据库
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";

            StrInsert = "insert into Subject (name) values (@name)";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值

            cmd.Parameters.Add("@name", SqlDbType.VarChar);
            cmd.Parameters["@name"].Value = subject;

            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();
        }
        #endregion

        #region 修改科目
        public static void updateSubjectManager(string id, string name)
        {
            //连接数据库
            SqlConnection sqlcon1 = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon1.Open();
            //修改数据信息
            string strSqls = "";

            strSqls = "update Subject set name = '" + name + "' where id = '" + id + "'";

            SqlCommand cmd = new SqlCommand(strSqls, sqlcon1);
            //添加参数并且设置参数值
            cmd.ExecuteNonQuery();
            sqlcon1.Close();
        }
        #endregion

        #region 获取科目列表
        public static List<SubjectModel> getSubjectList()
        {
            string sqlStr = "select  * from Subject";


            DataRowCollection dataRows = SQLTool.sqlGetListManager(sqlStr);
            int rows = dataRows.Count;

            List<SubjectModel> subjectList = new List<SubjectModel>();

            for (int i = 0; i < rows; i++)
            {
                SubjectModel subjectModel = new SubjectModel();
                subjectModel.id = dataRows[i]["id"].ToString();
                subjectModel.name = dataRows[i]["name"].ToString();
                subjectModel.image = dataRows[i]["image"].ToString();
                subjectList.Add(subjectModel);
            }

            return subjectList;

        }
        #endregion

        #region 删除科目列表
        public void deleteSubject(string id)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";
            StrInsert = "delete from Subject where id = '" + id + "'";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值

            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();

        }
        #endregion
        #endregion

        #region 二级科目
        #region 添加科目
        public void insertSubSubject(string subjectid,string subSubject)
        {
            // 连接数据库
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";

            StrInsert = "insert into Sub_Subject (name,parent_id) values (@name,@parent_id)";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值

            cmd.Parameters.Add("@name", SqlDbType.VarChar);
            cmd.Parameters["@name"].Value = subSubject;
            cmd.Parameters.Add("@parent_id", SqlDbType.VarChar);
            cmd.Parameters["@parent_id"].Value = subjectid;
            

            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();
        }
        #endregion

        #region 修改科目
        public void updateSubSubjectManager(string id, string name)
        {
            //连接数据库
            SqlConnection sqlcon1 = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon1.Open();
            //修改数据信息
            string strSqls = "";

            strSqls = "update Sub_Subject set name = '" + name + "' where id = '" + id + "'";

            SqlCommand cmd = new SqlCommand(strSqls, sqlcon1);
            //添加参数并且设置参数值
            cmd.ExecuteNonQuery();
            sqlcon1.Close();
        }
        #endregion

        #region 获取科目列表
        public static List<Sub_SubjectModel> getSubSubjectList(string subjectid)
        {
            string sqlStr = "";
            if (subjectid == "")
            {
                sqlStr = "select  * from Sub_Subject";
            }
            else {
                sqlStr = "select  * from Sub_Subject where parent_id = '" + subjectid + "'";
            }
           

            DataRowCollection dataRows = SQLTool.sqlGetListManager(sqlStr);
            int rows = dataRows.Count;

            List<Sub_SubjectModel> subjectList = new List<Sub_SubjectModel>();

            for (int i = 0; i < rows; i++)
            {
                Sub_SubjectModel subjectModel = new Sub_SubjectModel();
                subjectModel.id = dataRows[i]["id"].ToString();
                subjectModel.part_id = dataRows[i]["part_id"].ToString();
                subjectModel.name = dataRows[i]["name"].ToString();
                subjectModel.parent_id = dataRows[i]["parent_id"].ToString();
                subjectModel.ablum = dataRows[i]["ablum"].ToString();
                subjectModel.description = dataRows[i]["description"].ToString();

                subjectList.Add(subjectModel);

            }

            return subjectList;

        }
        #endregion

        #region 删除科目列表
        public void deleteSubSubject(string id)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";
            StrInsert = "delete from Sub_Subject where id = '" + id + "'";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值

            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();

        }
        #endregion
        #endregion

    }
}